﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class BunkerPOSQL
    {
        public static string GetAllStemmedBunkerPOAndQutation()
        {
            return @"select 
                    distinct vBSEnquirySearch.EnquiryID enquiry_no,
                    vBSEnquirySearch.EnquiryDate enquiry_date,
                    vBSEnquirySearch.VesselName vessel,
                    vVMVoyageSrch.Vessel vessel_code,
                    vBSEnquirySearch.Port port,
                    vBSEnquirySearch.BuyerName buyer,
                    vBSEnquirySearch.Remarks buyer_remarks,
                    vBSEnquirySearch.DeliveryDate delivery_date,
                    vSupplierSrch.SupplierName supplier,
                    vBSEnquiryBunkerLifting.Quality grade,
                    vBSEnqBLQuotationSearch.DeliveryLumpsum delivery_lumpsum_price,
                    vBSEnqBLQuotationSearch.Price price_per_mt,
                    vBSEnqBLQuotationSearch.QuotationID quotation_id,
                    vBSEnqBLQuotationSearch.SupplierName supplier_quotation,
                    vBSEnqBLQuotationSearch.Status quotation_status,
                    vBSEnquiryBunkerLifting.StemDate stemmed_date,
                    vBSEnquiryBunkerLifting.StemQty quantity_stemmed, 
                    vBSEnquirySearch.Status enquiry_status,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vLiftingSrch.GrossPriceUSD price_gross,
                    vLiftingSrch.BrokerExpense expense_broker,
                    vLiftingSrch.DelExpense expense_delivery,
                    vLiftingSrch.LiftingStatus lifting_status,
                    vVMVoyBunkerSuppDueSrch.DueDate due,
                    vVMVoyageSrch.OperatedBy operated_by,
                    vBSEnquiryBunkerLifting.EstQtyMax est_qty_max
                    from 
                    vBSEnquirySearch
                    LEFT OUTER JOIN vBSEnquiryBunkerLifting ON vBSEnquiryBunkerLifting.Enquiry = vBSEnquirySearch.EnquiryID
                    LEFT OUTER JOIN vBSEnqBLQuotationSearch ON vBSEnqBLQuotationSearch.BunkerLifting = vBSEnquiryBunkerLifting.BunkerLiftingID
                    LEFT OUTER JOIN vVMVoyageSrch ON vBSEnquirySearch.VMVOYAGE = vVMVoyageSrch.ID 
                    LEFT OUTER JOIN vProfitCentreOrganisationSrch ON vVMVoyageSrch.ID = vProfitCentreOrganisationSrch.ID 
                    LEFT OUTER JOIN vVMVoyagePortSrch ON vVMVoyageSrch.ID=vVMVoyagePortSrch.VOYAGE AND vBSEnquirySearch.VMVOYAGEPORT = vVMVoyagePortSrch.ID 
                    LEFT OUTER JOIN vSupplierSrch ON vVMVoyagePortSrch.ID = vSupplierSrch.PORT 
                    JOIN vLiftingSrch ON vSupplierSrch.ID = vLiftingSrch.VMVoyagePortBunkerSupplier AND vLiftingSrch.ID = vBSEnquiryBunkerLifting.VMVoyagePortBunkerLifting 
                    LEFT OUTER JOIN vVMVoyBunkerSuppDueSrch ON vSupplierSrch.ID = vVMVoyBunkerSuppDueSrch.ID 
                    where 
                    vBSEnquiryBunkerLifting.BunkerLiftingID is not null and
                    vLiftingSrch.LiftingStatus is not null
                    and vLiftingSrch.LiftingStatus in ('Stemmed','Delivered')
                    and vBSEnquiryBunkerLifting.StemDate > '2013-07-23 00:00'    
                    and  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACCSHIP (UK) LTD'                              
                    --and vBSEnquirySearch.EnquiryID in (996)
                    OR
                    (
                    vBSEnquiryBunkerLifting.BunkerLiftingID is not null and
                    vLiftingSrch.LiftingStatus is not null
                    and vLiftingSrch.LiftingStatus in ('Stemmed','Delivered')
                    and vBSEnquiryBunkerLifting.StemDate > '2014-01-01 00:00'    
                    and  vProfitCentreOrganisationSrch.ProfitCentreName = 'PACCSHIP (UK) LTD'                              
                    )
                    order by vBSEnquiryBunkerLifting.StemDate
                    ";
        }
        public static string GetAllStemmedBunkerPOAndQutationByEnquiryNumber()
        {
            return @"select 
                    distinct vBSEnquirySearch.EnquiryID enquiry_no,
                    vBSEnquirySearch.EnquiryDate enquiry_date,
                    vBSEnquirySearch.VesselName vessel,
                    vVMVoyageSrch.Vessel vessel_code,
                    vBSEnquirySearch.Port port,
                    vBSEnquirySearch.BuyerName buyer,
                    vBSEnquirySearch.Remarks buyer_remarks,
                    vBSEnquirySearch.DeliveryDate delivery_date,
                    vSupplierSrch.SupplierName supplier,
                    vBSEnquiryBunkerLifting.Quality grade,
                    vBSEnqBLQuotationSearch.DeliveryLumpsum delivery_lumpsum_price,
                    vBSEnqBLQuotationSearch.Price price_per_mt,
                    vBSEnqBLQuotationSearch.QuotationID quotation_id,
                    vBSEnqBLQuotationSearch.SupplierName supplier_quotation,
                    vBSEnqBLQuotationSearch.Status quotation_status,
                    vBSEnquiryBunkerLifting.StemDate stemmed_date,
                    vBSEnquiryBunkerLifting.StemQty quantity_stemmed, 
                    vBSEnquirySearch.Status enquiry_status,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vLiftingSrch.GrossPriceUSD price_gross,
                    vLiftingSrch.BrokerExpense expense_broker,
                    vLiftingSrch.DelExpense expense_delivery,
                    vLiftingSrch.LiftingStatus lifting_status,
                    vVMVoyBunkerSuppDueSrch.DueDate due,
                    vVMVoyageSrch.OperatedBy operated_by,
                    vBSEnquiryBunkerLifting.EstQtyMax est_qty_max
                    from 
                    vBSEnquirySearch
                    LEFT OUTER JOIN vBSEnquiryBunkerLifting ON vBSEnquiryBunkerLifting.Enquiry = vBSEnquirySearch.EnquiryID
                    LEFT OUTER JOIN vBSEnqBLQuotationSearch ON vBSEnqBLQuotationSearch.BunkerLifting = vBSEnquiryBunkerLifting.BunkerLiftingID
                    LEFT OUTER JOIN vVMVoyageSrch ON vBSEnquirySearch.VMVOYAGE = vVMVoyageSrch.ID 
                    LEFT OUTER JOIN vProfitCentreOrganisationSrch ON vVMVoyageSrch.ID = vProfitCentreOrganisationSrch.ID 
                    LEFT OUTER JOIN vVMVoyagePortSrch ON vVMVoyageSrch.ID=vVMVoyagePortSrch.VOYAGE AND vBSEnquirySearch.VMVOYAGEPORT = vVMVoyagePortSrch.ID 
                    LEFT OUTER JOIN vSupplierSrch ON vVMVoyagePortSrch.ID = vSupplierSrch.PORT 
                    JOIN vLiftingSrch ON vSupplierSrch.ID = vLiftingSrch.VMVoyagePortBunkerSupplier AND vLiftingSrch.ID = vBSEnquiryBunkerLifting.VMVoyagePortBunkerLifting 
                    LEFT OUTER JOIN vVMVoyBunkerSuppDueSrch ON vSupplierSrch.ID = vVMVoyBunkerSuppDueSrch.ID 
                    where 
                    vBSEnquiryBunkerLifting.BunkerLiftingID is not null and
                    vLiftingSrch.LiftingStatus is not null
                    --and vLiftingSrch.LiftingStatus = 'Stemmed'
                    and vBSEnquiryBunkerLifting.StemDate > '2013-07-23 00:00'    
                    and  vProfitCentreOrganisationSrch.ProfitCentreName <> 'PACCSHIP (UK) LTD'                              
                    and vBSEnquirySearch.EnquiryID = @enquiry_no
                    OR
                    (
                    vBSEnquiryBunkerLifting.BunkerLiftingID is not null and
                    vLiftingSrch.LiftingStatus is not null
                    --and vLiftingSrch.LiftingStatus = 'Stemmed'
                    and vBSEnquiryBunkerLifting.StemDate > '2014-01-01 00:00'    
                    and  vProfitCentreOrganisationSrch.ProfitCentreName = 'PACCSHIP (UK) LTD'   
                    and vBSEnquirySearch.EnquiryID = @enquiry_no                    
                    )
                    order by vBSEnquiryBunkerLifting.StemDate
                    ";
        }
        public static string GetAllBunkerPOHeader()
        {
            return @"
                Select distinct vSupplierSrch.SupplierName supplier,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vLiftingSrch.StemmedDate stemmed_Date,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vLiftingSrch.Stemmed quantity_stemmed,
                    vLiftingSrch.GrossPriceUSD price_gross,
                    vLiftingSrch.BrokerExpense expense_broker,
                    vLiftingSrch.DelExpense expense_delivery,
                    vLiftingSrch.LiftingStatus lifting_status,
                    vVMVoyBunkerSuppDueSrch.DueDate due,
                    vBSEnquirySearch.EnquiryDate enquiry_Date,
                    vBSEnquirySearch.EnquiryID enquiry_no,
                    vBSEnquirySearch.Status enquiry_status,
                    null vessel, null Port,null Buyer,null buyer_remarks,null delivery_date,
                    null grade,null delivery_lumpsum_price, null price_per_mt,null quotation_id, null quotation_status
                    --Cast(vLiftingSrch.StemmedDate as float)+2 Calc_StemmedDate,
                    --Cast(vVMVoyBunkerSuppDueSrch.DueDate as float)+2 Calc_DueDate,
                    --Cast(vBSEnquirySearch.EnquiryDate as float)+2 Calc_EnquiryDate
                FROM VVMVOYAGESRCH JOIN VBSENQUIRYSEARCH ON VBSENQUIRYSEARCH.VMVOYAGE = VVMVOYAGESRCH.ID JOIN VVMVOYAGEPORTSRCH ON VVMVOYAGESRCH.ID=VVMVOYAGEPORTSRCH.VOYAGE AND 
                VBSENQUIRYSEARCH.VMVOYAGEPORT = VVMVOYAGEPORTSRCH.ID JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID JOIN DELTAVIEWPERMSETTING ON VVMVOYAGESRCH.COMPANY=DELTAVIEWPERMSETTING.DEPT JOIN VSUPPLIERSRCH ON VVMVOYAGEPORTSRCH.ID = VSUPPLIERSRCH.PORT JOIN VBSENQUIRYBUNKERLIFTING ON VBSENQUIRYSEARCH.ENQUIRYID = VBSENQUIRYBUNKERLIFTING.ENQUIRY JOIN VLIFTINGSRCH ON VSUPPLIERSRCH.ID = VLIFTINGSRCH.VMVOYAGEPORTBUNKERSUPPLIER AND 
                VLIFTINGSRCH.ID = VBSENQUIRYBUNKERLIFTING.VMVOYAGEPORTBUNKERLIFTING JOIN VVMVOYBUNKERSUPPDUESRCH ON VSUPPLIERSRCH.ID = VVMVOYBUNKERSUPPDUESRCH.ID CROSS JOIN VSSLCLIENTCONFIG 
                WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed'
                --AND (vLiftingSrch.LiftingStatus = 'Stemmed')    
            ";
        }

        public static string GetAllBunkerPODetails()
        {
            return @"
               Select distinct vBSEnquirySearch.EnquiryID enquiry_no,
                vBSEnquirySearch.EnquiryDate enquiry_date,
                vBSEnquirySearch.VesselName vessel,
                vBSEnquirySearch.Port port,
                vBSEnquirySearch.BuyerName buyer,
                vBSEnquirySearch.Remarks buyer_remarks,
                vBSEnquirySearch.DeliveryDate delivery_date,
                vBSEnquiryBunkerLifting.Quality grade,
                vBSEnqBLQuotationSearch.DeliveryLumpsum delivery_lumpsum_price,
                vBSEnqBLQuotationSearch.Price price_per_mt,
                vBSEnqBLQuotationSearch.QuotationID quotation_id,
                vBSEnqBLQuotationSearch.SupplierName supplier,
                vBSEnquiryBunkerLifting.StemDate stemmed_date,
                vBSEnquiryBunkerLifting.StemQty quantity_stemmed,
                vBSEnqBLQuotationSearch.Status quotation_status,
                vBSEnquirySearch.Status enquiry_status,
                null profit_centre_fullname,
                null voyage_no,
                null price_gross,
                null expense_broker,
                null expense_delivery,
                null lifting_status,
                null due
                --Cast(vBSEnquirySearch.EnquiryDate as float)+2 Calc_EnquiryDate,
                --Cast(vBSEnquirySearch.DeliveryDate as float)+2 Calc_DeliveryDate,
                --Cast(vBSEnquiryBunkerLifting.StemDate as float)+2 Calc_StemDate
                 FROM VBSENQUIRYBUNKERLIFTING 
                 JOIN VBSENQBLQUOTATIONSEARCH ON VBSENQUIRYBUNKERLIFTING.BUNKERLIFTINGID=VBSENQBLQUOTATIONSEARCH.BUNKERLIFTING 
                 JOIN VBSENQUIRYSEARCH ON VBSENQUIRYSEARCH.ENQUIRYID=VBSENQUIRYBUNKERLIFTING.ENQUIRY 
                 --WHERE (vBSEnquiryBunkerLifting.StemDate >= '14May12 0:00:00') 
                 WHERE vBSEnqBLQuotationSearch.Status = 'Stem' --tambahan sendiri
                 --ORDER BY vBSEnquirySearch.EnquiryID, vBSEnquirySearch.EnquiryDate, vBSEnquiryBunkerLifting.Quality
            ";
        }

        public static string GetAllBunkerPODetailsByHeader()
        {
            return @"
               Select distinct vBSEnquirySearch.EnquiryID enquiry_no,
                vBSEnquirySearch.EnquiryDate enquiry_date,
                vBSEnquirySearch.VesselName vessel,
                vBSEnquirySearch.Port port,
                vBSEnquirySearch.BuyerName buyer,
                vBSEnquirySearch.Remarks buyer_remarks,
                vBSEnquirySearch.DeliveryDate delivery_date,
                vBSEnquiryBunkerLifting.Quality grade,
                vBSEnqBLQuotationSearch.DeliveryLumpsum delivery_lumpsum_price,
                vBSEnqBLQuotationSearch.Price price_per_mt,
                vBSEnqBLQuotationSearch.QuotationID quotation_id,
                vBSEnqBLQuotationSearch.SupplierName supplier,
                vBSEnquiryBunkerLifting.StemDate stemmed_date,
                vBSEnquiryBunkerLifting.StemQty quantity_stemmed,
                vBSEnqBLQuotationSearch.Status quotation_status,
                vBSEnquirySearch.Status enquiry_status,
                null profit_centre_fullname,
                null voyage_no,
                null price_gross,
                null expense_broker,
                null expense_delivery,
                null lifting_status,
                null due
                --Cast(vBSEnquirySearch.EnquiryDate as float)+2 Calc_EnquiryDate,
                --Cast(vBSEnquirySearch.DeliveryDate as float)+2 Calc_DeliveryDate,
                --Cast(vBSEnquiryBunkerLifting.StemDate as float)+2 Calc_StemDate
                 FROM VBSENQUIRYBUNKERLIFTING 
                 JOIN VBSENQBLQUOTATIONSEARCH ON VBSENQUIRYBUNKERLIFTING.BUNKERLIFTINGID=VBSENQBLQUOTATIONSEARCH.BUNKERLIFTING 
                 JOIN VBSENQUIRYSEARCH ON VBSENQUIRYSEARCH.ENQUIRYID=VBSENQUIRYBUNKERLIFTING.ENQUIRY 
                 --WHERE (vBSEnquiryBunkerLifting.StemDate >= '14May12 0:00:00') 
                 WHERE vBSEnquirySearch.EnquiryID = @enquiry_no
                    and vBSEnqBLQuotationSearch.Status = 'Stem' --tambahan sendiri
                   and vBSEnquiryBunkerLifting.StemQty = @quantity_stemmed
                 --ORDER BY vBSEnquirySearch.EnquiryID, vBSEnquirySearch.EnquiryDate, vBSEnquiryBunkerLifting.Quality
            ";
        }

        public static string CountBunkerPOTemp()
        {
            return @"
            select count(*) from i_bunker_po_temp
            where
                headerdetail = @headerdetail
               and supplier= @supplier
               and supplier_quotation= @supplier_quotation
               and profit_centre_fullname=@profit_centre_fullname
               and stemmed_date = @stemmed_date
               and voyage_no=@voyage_no
               and quantity_stemmed=@quantity_stemmed
               and price_gross=@price_gross
               and expense_broker=@expense_broker
               and expense_delivery=@expense_delivery
               and lifting_status=@lifting_status
               and due=@due
               and enquiry_date=@enquiry_date
               and enquiry_no=@enquiry_no               
               and vessel=@vessel
               and port=@port
               and buyer=@buyer
               and buyer_remarks=@buyer_remarks
               and delivery_date=@delivery_date
               and grade=@grade
               and delivery_lumpsum_price=@delivery_lumpsum_price
               and price_per_mt=@price_per_mt
               and quotation_id=@quotation_id
               and quotation_status=@quotation_status
                and operated_by=@operated_by
            ";

            //and enquiry_status=@enquiry_status
        }
        public static string InsertBunkerPOTemp()
        {
            return @"INSERT INTO [i_bunker_po_temp]
           (
            [pickup_flag]
           ,[insert_date]
           ,[headerdetail]
           ,[supplier]
           ,[profit_centre_fullname]
           ,[stemmed_date]
           ,[voyage_no]
           ,[quantity_stemmed]
           ,[price_gross]
           ,[expense_broker]
           ,[expense_delivery]
           ,[lifting_status]
           ,[due]
           ,[enquiry_date]
           ,[enquiry_no]
           ,[enquiry_status]
           ,[vessel]
           ,[port]
           ,[buyer]
           ,[buyer_remarks]
           ,[delivery_date]
           ,[grade]
           ,[delivery_lumpsum_price]
           ,[price_per_mt]
           ,[quotation_id]
           ,[quotation_status]
           ,[supplier_quotation]
            ,[operated_by]
            ,[vessel_code]
            ,[est_qty_max]
            )
     VALUES
           (
            @pickup_flag
           ,@insert_date
           ,@headerdetail
           ,@supplier
           ,@profit_centre_fullname
           ,@stemmed_date
           ,@voyage_no
           ,@quantity_stemmed
           ,@price_gross
           ,@expense_broker
           ,@expense_delivery
           ,@lifting_status
           ,@due
           ,@enquiry_date
           ,@enquiry_no
           ,@enquiry_status
           ,@vessel
           ,@port
           ,@buyer
           ,@buyer_remarks
           ,@delivery_date
           ,@grade
           ,@delivery_lumpsum_price
           ,@price_per_mt
           ,@quotation_id
           ,@quotation_status
            ,@supplier_quotation
            ,@operated_by
            ,@vessel_code
            ,@est_qty_max
            )";
        }

        public static string GetAllBunkerPOHeaderOutstandingInterface()
        {
            return @"select * from i_bunker_po_temp where headerdetail ='H' and pickup_flag='0'";
        }
        public static string GetAllBunkerPOOutstandingEnquiryInterface()
        {
            return @"select distinct(enquiry_no) from i_bunker_po_temp where pickup_flag='0' and supplier <> ''";// AND enquiry_date > '2013-07-23'";
        }
        public static string GetAllBunkerPODetailsOutstandingInterfaceByHeader()
        {
            return @"select * from i_bunker_po_temp where headerdetail ='D' and pickup_flag='PENDING' 
            and quantity_stemmed=@quantity_stemmed and enquiry_no=@enquiry_no";
        }
        public static string UpdatePOTempPickup_flagStatus()
        {
            return @"update i_bunker_po_temp
            set 
            pickup_flag = @pickup_flag 
            , pickup_date = @pickup_date 
            , pickup_update_date=@pickup_update_date
            , agrtidList = @agrtidList
            , remarks = @remarks
            , batch_id=@batch_id
            where po_interface_id=@po_interface_id
               ";
        }
        public static string GetAllBunkerPOItemStemQuotationByEnquiryNo()
        {
            return @"
                select * from i_bunker_po_temp where pickup_flag='0' and quotation_status in ('', 'Stem') and enquiry_no = @enquiry_no and supplier <> '' and lifting_status <> ''";
        }
        public static string GetBunkerFirstPOItemStemByEnquiryNo()
        {
            return @"
                select * from i_bunker_po_temp where pickup_flag='0' and quotation_status in ('', 'Stem') and enquiry_no = @enquiry_no and supplier <> '' ";
        }
        public static string GetAllBunkerPOItemAllQuotationByEnquiryNo()
        {
            return @"
                select * from i_bunker_po_temp where pickup_flag='0' and enquiry_no = @enquiry_no ";
        }

        public static string GetAllPendingPendingBunkerPO()
        {
            return @"select distinct(enquiry_no), enquiry_date, convert(char, pickup_date, 101) pickup_date, remarks  from i_bunker_po_temp where pickup_flag = 0  order by enquiry_date";
        }

        public static string GetAllPendingPendingBunkerPOCount()
        {
            return @"select count(*) from i_bunker_po_temp where pickup_flag = 0";
        }

        public static string GetAllPendingPendingBunkerPO(string p)
        {
            return string.Format(@"select distinct(enquiry_no), enquiry_date, convert(char, pickup_date, 101) pickup_date, remarks 
                from i_bunker_po_temp where pickup_flag = 0  
                and enquiry_no +  replace(convert(char(11), enquiry_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
                order by enquiry_date", p);
        }

        public static string GetAllBunkerPOByEnquiryNumber()
        {
            return @"select top 1 * from i_bunker_po_temp where enquiry_no = @enquiry_no and grade=@grade and quotation_status in ('', 'Stem')";// and lifting_status = 'Stemmed' 
        }

        public static string GetAllBunkerPOEnquiry()
        {
            return "select distinct enquiry_no  from i_bunker_po_temp where pickup_flag in (0,1,2)";
        }

        public static string GetAllAgressoPO()
        {
            return @"select distinct '0' order_id, ext_ord_ref, client, order_date
                        from apoheader where 
                        order_type = 'VK' 
                        and voucher_type = 'SP' 
                        and responsible = 'DESL'
                        and ext_ord_ref != ''
                        order by order_date";
        }
        public static string GetPOByExtOrdRef()
        {
            return @"select distinct *
                        from i_bunker_po_temp where pickup_flag = 1 and stemmed_date = @stemmed_date
                        and (enquiry_no+'_'+
                        right('00'+ convert(nvarchar,DAY(enquiry_date)), '2')+
                        right('00'+ convert(nvarchar,MONTH(enquiry_date)), '2')+
                        right('00'+ convert(nvarchar,YEAR(enquiry_date)), '4')) =
                        @ext_ord_ref";
        }
    }
}
